/*
* Copyright (C) 2012 Manuel Pérez
*
* This file is part of OpenSudoku.
*
* OpenSudoku is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* OpenSudoku is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with OpenSudoku. If not, see <http://www.gnu.org/licenses/>.
*
*/
package dBasea;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Istripu {

	public boolean existitzenDaIstripua(String idIstripu) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM Abokatua WHERE idIstripua='" + idIstripu
				+ "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		return aurki;
	}

	public int getHiria(int idItripu) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT idHiria FROM Abokatua where idIstripu="
				+ idItripu;
		ResultSet rs = dbk.executeSQL(query);
		int x = 0;
		while (rs.next()) {
			x = rs.getInt(1);
		}
		return x;
	}

	public void istripuAldatu(int istri, String tailer, String peritu,
			String abokatu, String oharrak, String helbidea, String hiria)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "UPDATE Istripua SET idIstripua="
				+ istri
				+ ", idTailerra = (select idTailerra from Tailerra where tailerIzena = '"
				+ tailer
				+ "')"
				+ ", idPeritua = (select idPeritua from Peritua where izena = '"
				+ peritu
				+ "')"
				+ ", idAbokatua =(select idAbokatua from Abokatua where izena = '"
				+ abokatu + "')" + ", oharrak = '" + oharrak + "',helbidea = '"
				+ helbidea
				+ "',idHiria = (select izenaHiria from Hiria where idHiria = '"
				+ hiria + "') " + "WHERE idIstripua = " + istri + " ";
		dbk.executeSQL(query);

	}

	public void istriSortu(int istri, String tailer, String peritu,
			String abokatu, String oharrak, String helbidea, String hiria)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO Istripua (idIstripua,idTailerra,idPeritua,idAbokatua,oharrak,helbidea, idHiria)"
				+ " VALUES ("
				+ istri
				+ ",(select idTailerra from Tailerra where tailerIzena = '"
				+ tailer
				+ "'),(select idPeritua from Peritua where izena = '"
				+ peritu
				+ "'),(select idAbokatua from Abokatua where izena = '"
				+ abokatu
				+ "'),'"
				+ oharrak
				+ "','"
				+ helbidea
				+ "',(select idHiriaa from Hiriaa where izenaHiria = '"
				+ peritu + hiria + "') )";

		dbk.executeSQL(query);

	}

	public void istripuaEzabatu(int istri) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "DELETE FROM Istripua WHERE idIstripua=" + istri + " ";
		dbk.executeSQL(query);
	}

	public void aboIstripuEsleipena(int id, String abo) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "UPDATE Istripua SET idAbokatua =(Select idAbokatua from Abokatua where izena='"
				+ abo + "') WHERE idIstripua = " + id + " ";
		dbk.executeSQL(query);

	}

	public boolean dagoIstripua(String idIstripu) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM Istripua WHERE idIstripua='" + idIstripu
				+ "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		return aurki;
	}

	public int getHi(int idItripu) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT idHiria FROM Istripua where idIstripua="
				+ idItripu;
		ResultSet rs = dbk.executeSQL(query);
		int x = 0;
		while (rs.next()) {
			x = rs.getInt(1);
		}

		return x;
	}

	public int istriSortu(String helbidea, String hiria, String oharrak)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO Istripua (helbidea, idHiria, oharrak)"
				+ " VALUES ('" + helbidea
				+ "',(select idHiria from Hiria where izenaHiria = '" + hiria
				+ "')" + ", '" + oharrak + "')";

		dbk.executeSQL(query);

		dbk = DatuBasea.getInstance();
		query = "SELECT max(idIstripua) FROM Istripua";
		ResultSet rs = dbk.executeSQL(query);
		int id = -1;
		if (rs.next()) {
			id = rs.getInt(1);
		}

		return id;
	}

	public void inbolukratuakSortu(int istripuaId, String nan, String matrikula)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO Inbolukratuak " + " VALUES (" + istripuaId
				+ ", '" + nan + "'" + ", '" + matrikula + "'" + ")";

		dbk.executeSQL(query);
	}
}
